{Joins all records of all species from Specimens, Indiv_records_misc, and Indiv_records_std in a single table against lat and long}

{individual records from Specimens - joined on Georeference by georef_ID to get lat and lon}

select 
	specimens.species_id,specimens.date_time,
	georeference.latdecimal,georeference.longdecimal
from georeference inner join specimens
	on georeference.georef_id = specimens.georef_id join species
	on specimens.species_id=species.species_id 
	where species.taxa='frog' and georeference.longdecimal<>"" and georeference.latdecimal<>"" and specimens.date_time<>""
	
{individual records from Individ_records_misc - joined on Georeference by georef_ID to get lat and lon}

select 
	individ_records_misc.species_id,individ_records_misc.date_time,
	georeference.latdecimal,georeference.longdecimal
from georeference inner join individ_records_misc
	on georeference.georef_id = individ_records_misc.georef_id join species
	on individ_records_misc.species_id=species.species_id
	where species.taxa='frog' and georeference.longdecimal<>"" and georeference.latdecimal<>"" and individ_records_misc.date_time<>""


{individual records from Individ_records_std - joined on Samples by sample_ID, joined on Georeference by georef_ID to get lat and lon}	

select
	individ_records_std.species_id, samples.date_time,
	georeference.latdecimal,georeference.longdecimal	
from georeference inner join samples
	on samples.georef_id=georeference.georef_id join individ_records_std
	on individ_records_std.sample_id=samples.sample_id join species
	on individ_records_std.species_id=species.species_id
	where species.taxa='frog' and georeference.longdecimal<>"" and georeference.latdecimal<>"" and samples.date_time<>""




library(SDMTools)
work.dir='E:/frog_declines/';setwd(work.dir)

sp=read.csv('specimens.csv',header=F)
misc=read.csv('misc_records.csv',header=F)
std=read.csv('std_records.csv',header=F)

frogs=rbind(sp,misc,std)
write.csv(frogs, 'all_frogs.csv')
	

